<?php

use yii\db\Migration;
use yii\db\Query;


class m200003_170301_alert extends Migration
{
    public function up()
    {
        $tableOptions = null;
        if ($this->db->driverName === 'mysql') {
            // http://stackoverflow.com/questions/766809/whats-the-difference-between-utf8-general-ci-and-utf8-unicode-ci
            $tableOptions = 'CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE=InnoDB';
        }

        $this->createTable('{{%alert}}', [
            'id' => $this->primaryKey(),
            'Timestamp' => $this->bigInteger()->notNull(),
            'AlertID' => $this->bigInteger(),
            'SensorID' => $this->bigInteger(),
            'AlertType' => $this->smallInteger()->notNull()->defaultValue(0),
            'SrcType' =>$this->smallInteger()->notNull()->defaultValue(0),
            'Point' => $this->integer(),
            'IsSolveBy3rd' => $this->smallInteger()->notNull()->defaultValue(0),
            'Label' => $this->string(),

            'ExceptionAlertList' => $this->text(),
            'AlertFileList' => $this->text(),
            'AlertIPList' => $this->text(),
            'AlertURLList' => $this->text(),
            'HitRegulationList' => $this->text(),
            'EventList' => $this->getDb()->getSchema()->createColumnSchemaBuilder('longtext'),

            'status' => $this->smallInteger()->notNull()->defaultValue(0),
            'created_at' => $this->integer()->notNull(),
            'updated_at' => $this->integer()->notNull(),
        ], $tableOptions);

        $sql = 'CREATE VIEW `behEX` AS 
        SELECT 

            CONCAT("Beh_",alert.id) AS id,
            alert.id AS rid,
            
            "Beh" AS Type,

            alert.status,

            NULL AS MD5, 
            NULL AS SHA256,
            NULL AS IP,
            NULL AS URL,
            alert.Label,
            
            alert.AlertID, 
            alert.Point, 
            alert.SensorID, 
            alert.AlertType, 
            alert.SrcType, 
            alert.IsSolveBy3rd, 
            alert.`Timestamp`, 
            alert.updated_at, 
            alert.created_at, 
            sensor.ComputerName, 
            sensor.OSType, 
            sensor.OSTypeShort, 
            sensor.IP AS SensorIP
            
        FROM alert 
            INNER JOIN sensor ON sensor.SensorID = alert.SensorID
            WHERE alert.AlertType > 3';
        $command=$this->db->createCommand($sql);
        $rowCount=$command->execute();
        

    }

    public function down()
    {
        $this->db->createCommand('drop VIEW behEX;')->execute();
        $this->dropTable('{{%alert}}');
    }
}

